<? 
ob_start();
session_start();

if(empty($HTTP_SESSION_VARS['user_login'])){
	echo"
	<tr>
	 <td align=center colspan=3>
	 <br><br><br>".$_SESSION['user_login']."
		Maaf, Anda tidak bisa mengakses halaman ini, Anda harus login terlebih dahulu ... <br><br>
		[ <a href='$path/index.php'>silahkan login</a> ]
	 </td>
	</tr>
	";
	exit();
}

include("../lib/config.php");
include("../lib/function.php");
include("../lib/header.php");

$divisi 	= $HTTP_GET_VARS['divisi'];
$st			= $HTTP_GET_VARS['st'];
$stat_kar	= $HTTP_GET_VARS['stat_kar'];
$excel		= $HTTP_GET_VARS['excel'];
$jabatan	= $HTTP_GET_VARS['jabatan'];
$pendidikan	= $HTTP_GET_VARS['pendidikan'];
$golongan	= $HTTP_GET_VARS['golongan'];
$bulan1		= $HTTP_GET_VARS['bulan1'];
$bulan2		= $HTTP_GET_VARS['bulan2'];
$tahun1		= $HTTP_GET_VARS['tahun1'];
$tahun2		= $HTTP_GET_VARS['tahun2'];
$jns_kursus	= $HTTP_GET_VARS['jns_kursus'];
$excel		= $HTTP_GET_VARS['excel'];

// cek hak akses untuk cabang
$sad = mssql_query("SELECT nama_menu FROM VPrivUser A, VCabang B WHERE username='".$HTTP_SESSION_VARS['user_login']."' AND baca='1' AND A.nama_menu=B.Kode_Cabang",$conn);
while($rad = mssql_fetch_array($sad)){
// VARIABLE UNTUK CABANG
$VAreaCode[] = $rad[0];
}
/////////////////////////////////

if(empty($tahun1)){
	$tahun1 = 1980;
	$bulan1 = 1;
}

if(empty($tahun2)){
	$tahun2 = date("Y");
	$bulan2 = 12;
}

//$tgl_awal 	= date("m",mktime(0,0,0,$bulan1,1,$tahun1))."/".date("t",mktime(0,0,0,$bulan1,1,$tahun1))."/".$tahun1;
//$tgl_akhir 	= date("m",mktime(0,0,0,$bulan2,1,$tahun2))."/".date("t",mktime(0,0,0,$bulan2,1,$tahun2))."/".$tahun2;

switch($bulan2){
	case 1 	: $dd = 31;
	case 2 	: $dd = 29;
	case 3 	: $dd = 31;
	case 4 	: $dd = 30;
	case 5 	: $dd = 31;
	case 6 	: $dd = 30;
	case 7 	: $dd = 31;
	case 8 	: $dd = 31;
	case 9 	: $dd = 30;
	case 10 : $dd = 31;
	case 11 : $dd = 30;
	case 12 : $dd = 31;
}

$tgl_awal	= $bulan1."/01/".$tahun1;
$tgl_akhir	= $bulan2."/".$dd."/".$tahun2;
	
if($excel == "yes"){
}
else{
	?>
<title>Laporan Pegawai</title>
<script type="text/javascript">
function Show_Excel(){
	var divisi 		= document.forms[0].elements['divisi'].value;
	var stat_ker 	= document.forms[0].elements['stat_kar'].value;
	var stat_kar 	= document.forms[0].elements['st'].value;
	var jabatan		= document.forms[0].elements['jabatan'].value;
	var pendidikan 	= document.forms[0].elements['pendidikan'].value;
	var golongan	= document.forms[0].elements['golongan'].value;
	var tahun1 		= document.forms[0].elements['tahun1'].value;
	var tahun2 		= document.forms[0].elements['tahun2'].value;
	var bulan1		= document.forms[0].elements['bulan1'].value;
	var bulan2	 	= document.forms[0].elements['bulan2'].value;
	
	top.location.href = "<?=$PHP_SELF?>?divisi="+divisi+"&st="+stat_kar+"&excel=yes&stat_kar="+stat_ker+"&pendidikan="+pendidikan+"&jabatan="+jabatan+"&golongan="+golongan+"&tahun1="+tahun1+"&tahun2="+tahun2+"&bulan1="+bulan1+"&bulan2="+bulan2+"";
}	
</script>
<link rel="stylesheet" type="text/css" href="../style/style.css" />
 <div style="font-size:14px; border-bottom:solid 1px #000000; font-weight:bold;">Laporan Pegawai</div><br>
<fieldset>
 <legend>Filter Data</legend>
 <form action="<?=$PHP_SELF?>" method="get">
 <table border="0">
  <tr>
   <td>Divisi/Biro/Cabang</td>
   <td><select style='width:350px;' name='divisi'>
	    <option value='all'>--semua--</option>
        <?		
		$sqljd  = "SELECT * FROM VLevelCabang WHERE status='1' ";
		$sqljd .= "AND AreaCode IN (";

		for($k=0;$k<count($VAreaCode);$k++){
			$sqljd .= "'$VAreaCode[$k]',";
		}

		$sqljd .= "'$VAreaCode[0]') ";		
		
		$sqljd .= " ORDER BY uraian ASC";
		
		$sq2 = mssql_query($sqljd,$conn);
		while($rq2 = mssql_fetch_array($sq2)){
			echo"<option value='".$rq2[kode_so]."'";
			if($divisi == $rq2[kode_so]){
				echo" selected";
			}
			echo">$rq2[uraian]</option>";
		}
		?>
        </select></td>
   <td align="right">Jabatan</td>
   <td><select name="jabatan">
	    <option value='all'>--semua--</option>
        <?
		$sag = mssql_query("SELECT * FROM MstJabatan ORDER BY jabatan ASC",$conn);
		while($rag = mssql_fetch_array($sag)){
			echo"<option value='".$rag[kode]."'";
			if($jabatan == $rag[kode]){
				echo" selected";
			}
			echo">$rag[jabatan]</option>";
		}
		?>
   </select></td>
  </tr>
  <tr>
   <td>Status Kerja</td>
   <td width="300"><select name='stat_kar' style='width:350px;'>
	    <option value='all'>--semua--</option>
        <?
	$sq = mssql_query("SELECT * FROM VStatusKaryawan",$conn);
	while($rq = mssql_fetch_array($sq)){
		echo"<option value='".$rq[kode]."'";
		if($stat_kar == $rq[kode]){
			echo" selected";
		}
		echo">$rq[Nama]</option>";
	}
		?>
        </select></td>
   <td align="right">Pendidikan</td>
   <td><input type="text" name="pendidikan" style='width:377px;' value="<? if(!empty($pendidikan)){ echo $pendidikan; }?>" /></td>
   </tr>
   <tr>
   <td>Status Karyawan</td>
   <td><select name="st">
   <option value="all">--semua--</option>
   <option value="bekerja" <? if($st=="bekerja"){ echo" selected"; } ?>>Bekerja</option>
   <option value="berhenti" <? if($st=="berhenti"){ echo" selected"; } ?>>Berhenti</option>
   </select></td>
   <td align="right">Golongan</td>
   <td><select name="golongan">
	    <option value='all' <? if($golongan == "all" || trim($golongan) == ""){ echo" selected"; } ?>>--semua--</option>
		<?
        	for($i=0;$i<=17;$i++){
				echo"<option value='$i'>$i</option>";
			}
		?>
      </select>
      </td>
  </tr>
  <tr>
   <td>Periode</td>
   <td colspan="3">
   <?
			 echo"<select name='bulan1'>
			  <option value=''";
			  if(empty($bulan1)){ echo" selected"; }
			  echo">--Bulan--</option>";
			  echo"
			   <option value='1'"; if($bulan1 == "1"){ echo" selected"; } else {} echo">Januari</option>
			   <option value='2'"; if($bulan1 == "2"){ echo" selected"; } else {} echo">Februari</option>
			   <option value='3'"; if($bulan1 == "3"){ echo" selected"; } else {} echo">Maret</option>
			   <option value='4'"; if($bulan1 == "4"){ echo" selected"; } else {} echo">April</option>
			   <option value='5'"; if($bulan1 == "5"){ echo" selected"; } else {} echo">Mei</option>
			   <option value='6'"; if($bulan1 == "6"){ echo" selected"; } else {} echo">Juni</option>
			   <option value='7'"; if($bulan1 == "7"){ echo" selected"; } else {} echo">Juli</option>
			   <option value='8'"; if($bulan1 == "8"){ echo" selected"; } else {} echo">Agustus</option>
			   <option value='9'"; if($bulan1 == "9"){ echo" selected"; } else {} echo">September</option>
			   <option value='10'"; if($bulan1 == "10"){ echo" selected"; } else {} echo">Oktober</option>
			   <option value='11'"; if($bulan1 == "11"){ echo" selected"; } else {} echo">November</option>
			   <option value='12'"; if($bulan1 == "12"){ echo" selected"; } else {} echo">Desember</option>
			 </select>
			 <select name='tahun1'>
			  <option value=''>--Tahun--</option>";
				for($t1=1980;$t1<=2100;$t1++){
					echo"<option value='$t1'";
					if($tahun1 == $t1){
						echo" selected";
					}
					echo">$t1</option>";
				}
			  echo"
			 </select>
			 s.d
			 <select name='bulan2'>
			  <option value=''";
			  if(empty($bulan2)){ echo"selected"; }			  
			  echo">--Bulan--</option>
			   <option value='1'"; if($bulan2 == "1"){ echo" selected"; } else {} echo">Januari</option>
			   <option value='2'"; if($bulan2 == "2"){ echo" selected"; } else {} echo">Februari</option>
			   <option value='3'"; if($bulan2 == "3"){ echo" selected"; } else {} echo">Maret</option>
			   <option value='4'"; if($bulan2 == "4"){ echo" selected"; } else {} echo">April</option>
			   <option value='5'"; if($bulan2 == "5"){ echo" selected"; } else {} echo">Mei</option>
			   <option value='6'"; if($bulan2 == "6"){ echo" selected"; } else {} echo">Juni</option>
			   <option value='7'"; if($bulan2 == "7"){ echo" selected"; } else {} echo">Juli</option>
			   <option value='8'"; if($bulan2 == "8"){ echo" selected"; } else {} echo">Agustus</option>
			   <option value='9'"; if($bulan2 == "9"){ echo" selected"; } else {} echo">September</option>
			   <option value='10'"; if($bulan2 == "10"){ echo" selected"; } else {} echo">Oktober</option>
			   <option value='11'"; if($bulan2 == "11"){ echo" selected"; } else {} echo">November</option>
			   <option value='12'"; if($bulan2 == "12"){ echo" selected"; } else {} echo">Desember</option>
			 </select>
			 <select name='tahun2'>
			  <option value=''>--Tahun--</option>";
				for($t2=1980;$t2<=2100;$t2++){
					echo"<option value='$t2'";
					if($tahun2 == $t2){
						echo" selected";
					}
					echo">$t2</option>";
				}
			  echo"</select>";
			  ?>
   </td>
  </tr>
  <tr>
   <td>&nbsp;</td>
   <td><input type="submit" class="button" value="Cari"><input type="button" value="Kirim ke Excel" onclick="Javascript:Show_Excel();" class="button" /></td>
  </tr>
 </table>
 </form> 
</fieldset>
<?
// end if xecle
}

// awal doang

if(empty($divisi) && empty($st) && empty($stat_kar) && empty($excel) && empty($jabatan) && empty($pendidikan) && empty($golongan) && empty($jns_kursus)){

	exit;
}

	$sqlcount = "SELECT A.*, B.tgl_efektif tgl_masuk, D.nama FROM KarirPTSI A, VFirstKarir B, vlevelcabang C, karyawan D WHERE A.NIK=B.NIK AND A.kode_so2=C.kode_so and A.NIK=D.NIK ";
	$sqlcount .= "AND C.AreaCode IN (";

	for($k=0;$k<count($VAreaCode);$k++){
		$sqlcount .= "'$VAreaCode[$k]',";
	}

	$sqlcount .= "'$VAreaCode[0]') ";		
	
	if(!empty($st)){
		if($st == "all"){
		
		}
		else{
			if($st == "berhenti"){
				$sqlcount .= " AND A.Jenis IN ('U','H') ";			
			}
			elseif($st == "bekerja"){
				$sqlcount .= " AND A.Jenis NOT IN ('U','H') ";
			}
		}
	}
	
	if(!empty($divisi)){
		if($divisi == "all"){
		}
		else{
			$sqlcount .= " AND substring(A.kode_so2,1,7)='".substr($divisi,0,7)."'";
		}
	}
	
	if(!empty($stat_kar)){
		if($stat_kar == "all"){		
		}
		else{
			$sqlcount .= " AND A.stat_kar2='$stat_kar' ";
		}
	}
	
	if(!empty($jabatan)){
		if($jabatan == "all"){		
		}
		else{
			$sqlcount .= " AND A.jabatan2='$jabatan' ";
		}
	}
	
	// bulan dan tahun kursus
	if($bulan2+1 == 13){
		$bulanke2 = "01";
	}
	else{
		$bulanke2 = ($bulan2+1);
	}

	if($bulan1+1 == 13){
		$bulanke1 = "01";
	}
	else{
		$bulanke1 = ($bulan1+1);
	}
	
	// cek bulan dan tahun
	if(!empty($bulan1) && !empty($tahun1) && !empty($bulan2) && !empty($tahun2)){
		$sqlcount .= " and A.tgl_efektif between convert(datetime,'".$tgl_awal."') and convert(datetime,'".$tgl_akhir."')";
	}
	else{	
		if(!empty($bulan1) && !empty($tahun1) && empty($bulan2) && empty($tahun2)){
			$sqlcount .= " and A.tgl_efektif between convert(datetime,'".$tgl_awal."') and convert(datetime,'".$tgl_akhir."') ";
		}
		elseif(!empty($bulan2) && !empty($tahun2) && empty($bulan1) && empty($tahun1)){
			$sqlcount .= " and A.tgl_efektif between convert(datetime,'".$tgl_awal."') and convert(datetime,'".$tgl_akhir."') ";
		}	
	}
	
	// golongan
	if($golongan == "all" || $golongan == NULL){
	
	}
	else{
		$sqlcount .= " AND A.gol2='$golongan'";
	}
	
	// pendidikan
	if(empty($pendidikan)){
	
	}
	else{
		$sqlcount .= " AND A.nik IN (select NIK from pendidikan A, mstpendidikan B where A.Pend_ID=B.Pend_ID and jurusan like '%$pendidikan%')";	
	}

$result	= mssql_query($sqlcount,$conn);
$total	= mssql_num_rows($result);
$limit	= 20;
$pager  = Pager::getPagerData($total, $limit, $page);
$offset = $pager->offset;
$limit  = $pager->limit;
$page   = $pager->page;

//echo $sqlcount;

if($total > 0)
{
	$sql = "WITH AAA AS(SELECT ROW_NUMBER() OVER(ORDER BY D.Nama ASC) AS No, A.*, CONVERT(CHAR(12), A.tgl_efektif, 103) tgl_keluar, B.tgl_efektif tgl_masuk, CONVERT(CHAR(12), B.tgl_efektif, 103) tgl_masuky, D.nama FROM KarirPTSI A, VFirstKarir B, vlevelcabang C, karyawan as D WHERE A.NIK=B.NIK AND C.kode_so=A.kode_so2 and A.NIK=D.NIK ";
	///*
		$sql .= "AND C.AreaCode IN (";

		for($k=0;$k<count($VAreaCode);$k++){
			$sql .= "'$VAreaCode[$k]',";
		}

		$sql .= "'$VAreaCode[0]') ";		

	//*/
	if(!empty($st)){
		if($st == "all"){
		
		}
		else{
			if($st == "berhenti"){
				$sql .= " AND A.Jenis IN ('U','H') ";			
			}
			elseif($st == "bekerja"){
				$sql .= " AND A.Jenis NOT IN ('U','H') ";
			}
		}
	}
	
	if(!empty($divisi)){
		if($divisi == "all"){
		}
		else{
			$sql .= " AND substring(A.kode_so2,1,7)='".substr($divisi,0,7)."'";
		}
	}
	
	if(!empty($stat_kar)){
		if($stat_kar == "all"){		
		}
		else{
			$sql .= " AND A.stat_kar2='$stat_kar' ";
		}
	}
	
	if(!empty($jabatan)){
		if($jabatan == "all"){		
		}
		else{
			$sql .= " AND A.jabatan2='$jabatan' ";
		}
	}
	
	// bulan dan tahun kursus
	if($bulan2+1 == 13){
		$bulanke2 = "01";
	}
	else{
		$bulanke2 = ($bulan2+1);
	}

	if($bulan1+1 == 13){
		$bulanke1 = "01";
	}
	else{
		$bulanke1 = ($bulan1+1);
	}
	
	// cek bulan dan tahun
	if(!empty($bulan1) && !empty($tahun1) && !empty($bulan2) && !empty($tahun2)){
		$sql .= " and A.tgl_efektif between convert(datetime,'".$tgl_awal."') and convert(datetime,'".$tgl_akhir."')";
	}
	else{	
		if(!empty($bulan1) && !empty($tahun1) && empty($bulan2) && empty($tahun2)){
			$sql .= " and A.tgl_efektif between convert(datetime,'".$tgl_awal."') and convert(datetime,'".$tgl_akhir."') ";
		}
		elseif(!empty($bulan2) && !empty($tahun2) && empty($bulan1) && empty($tahun1)){
			$sql .= " and A.tgl_efektif between convert(datetime,'".$tgl_awal."') and convert(datetime,'".$tgl_akhir."') ";
		}	
	}
	
	// golongan
	if($golongan == "all" || $golongan == NULL){
	
	}
	else{
		$sql .= " AND A.gol2='$golongan'";
	}
	
	// pendidikan
	if(empty($pendidikan)){
	
	}
	else{
		$sql .= " AND A.nik IN (select NIK from pendidikan A, mstpendidikan B where A.Pend_ID=B.Pend_ID and jurusan like '%$pendidikan%')";	
	}
	
	$sql .= ") SELECT * FROM AAA";
	
	if($excel == "yes"){
	
	}
	else{
		$sql .= " WHERE No BETWEEN ".$offset." AND ".($offset+$limit)."";
	}
	
	$query = mssql_query($sql,$conn);
	
	if($excel == "yes"){
	
	}
	else{
		//PagingLap($pager,$page,$next,$limit,$keyword,$show,$conn,$st,$divisi,$stat_kar,$agama,$gender,$marital);
		PagingLapTr($pager,$page,$next,$limit,$keyword,$show,$conn,$st,$divisi,$stat_kar,$pendidikan,$jabatan,$golongan,$bulan1,$bulan2,$tahun2,$tahun1,$jns_kursus);
	}
	
	if($excel == "yes"){
	
	}
	else{
?>
<fieldset>
 <legend>Hasil Pencarian</legend>
<?
	}
?>
 <table width="100%">
  <tr class="headlist">
   <td>No.</td>
   <td>NIK</td>
   <td>Nama</td>
   <td>Divisi/Biro/Cabang</td>
   <td>Jabatan</td>
   <td>Fungsional</td>
   <td>Bagian</td>
   <td>Seksi</td>
   <td>Rangkap Jabatan</td>
   <td>Gol</td>
   <td>Status</td>
   <td>Status Kerja</td>
   <td>Tgl. Masuk</td>
   <td>Tgl. Keluar</td>
   <td>Pendidikan Terakhir</td>
  </tr>
  <?
  while($row = mssql_fetch_array($query)){
  ?>
  <tr class="contentlist" style="font-size:smaller;">
   <td align="right"><?=$row[No]?></td>
   <td><?=$row[NIK]?></td>
   <td><?=$row[nama]?></td>
   <td>
   <?
   // get divisi
	$kode_so = $row[kode_so2];
	if(empty($kode_so)){
		
	}
	else{
		$sdiv = mssql_query("SELECT uraian FROM VLevelCabang WHERE substring(kode_so,1,7)='".substr($kode_so,0,7)."'");
		$rdiv = mssql_fetch_array($sdiv);
		echo"$rdiv[0]";
	}
   ?>
   </td>
   <td>
   <?
   // get jabatan
   	if(empty($row[jabatan2])){
	
	}
	else{
		$jabatan = $row[jabatan2];
		$sjab	= mssql_query("SELECT Jabatan FROM MstJabatan WHERE kode='$jabatan'",$conn);
		$rjab	= mssql_fetch_array($sjab);
		echo"$rjab[0]";
	}
   ?>
   </td>
   <td>
   <?
   // get fungsional
   	if(empty($row[fungsi2])){
	
	}
	else{
		$fungsi = $row[fungsi2];
		$sfun	= mssql_query("SELECT Nama FROM MstFungsional WHERE kode='$fungsi'",$conn);
		$rfun	= mssql_fetch_array($sfun);
		echo"$rfun[0]";
	}
   ?>
   </td>
   <td>
   <?
   // get bagian
   	if(empty($kode_so)){
	
	}
	else{
		$sbag	= mssql_query("SELECT uraian FROM VLevelBagian WHERE substring(kode_so,1,10)='".substr($kode_so,0,10)."'",$conn);
		$rbag	= mssql_fetch_array($sbag);
		echo"$rbag[0]";
	}
   ?>
   </td>
   <td>
   <?
   // get bagian
   // 010000100100
   
   	if(empty($kode_so)){
	
	}
	else{
		$ssek	= mssql_query("SELECT uraian FROM VLevelSeksi WHERE substring(kode_so,1,12)='".substr($kode_so,0,12)."'",$conn);
		$rsek	= mssql_fetch_array($ssek);
		echo"$rsek[0]";
	}
   ?>
   </td>
   <td>
   <?
   // get jabatan rangkap
   	if(empty($row[jabatan1])){
	
	}
	else{
		$jabatanr = $row[jabatan1];
		$sjabr	= mssql_query("SELECT Nama FROM MstJabatan WHERE kode='$jabatanr'",$conn);
		$rjabr	= mssql_fetch_array($sjabr);
		echo"$rjabr[0]";
	}
   ?>
   </td>
   <td align="right"><?=$row[gol2]?></td>
   <td>
   <?
   // status karyawan
   	if(empty($row[stat_kar2])){
	
	}
	else{
		$stat_kar2 = $row[stat_kar2];
		$sstat_kar2	= mssql_query("SELECT Nama FROM VStatusKaryawan WHERE kode='$stat_kar2'",$conn);
		$rstat_kar2	= mssql_fetch_array($sstat_kar2);
		echo"$rstat_kar2[0]";
	}
   ?>
   </td>
   <td>
    <?
    // status kerja
	if($row[Jenis] == "U" || $row[Jenis] == "H"){
		echo"Berhenti";
	}
	else{
		echo"Bekerja";
	}
	?>
   </td>
   <td>
   <?
   // tgl masuk
   //$stglmasuk = mssql_query("SELECT CONVERT(CHAR(12), tgl_efektif, 103) tgl FROM VFirstKarir WHERE NIK='$row[NIK]'",$conn);
   //$rtglmasuk = mssql_fetch_array($stglmasuk);
   echo"$row[tgl_masuky]";
   ?>
   </td>
   <td>
	<?
	if($row[Jenis] == "U" || $row[Jenis] == "H"){
		echo"$row[tgl_keluar]";
	}
	?>
   </td>
   <td>
   <?
  	// get pendidikan terakhir
	$slastpend = mssql_query("select A.*, B.Pend_ID, C.fakultas, C.jurusan from vreppendidikan A, Pendidikan B, MstPendidikan C WHERE A.NIK=B.NIK AND B.Pend_ID=C.Pend_ID AND A.NIK='$row[NIK]' AND A.jurusan LIKE '%$pendidikan%' AND C.keterangan=A.keterangan ORDER BY seqno desc ",$conn);
	if(mssql_num_rows($slastpend) > 0){
		$rlastpend = mssql_fetch_array($slastpend);
		echo"$rlastpend[keterangan] $rlastpend[jurusan]";
	}
   ?>
   </td>
  </tr>
  <?
  }
  ?>
 </table>
 <?
 	if($excel == "yes"){
		header('Content-type: application/vnd.ms-excel');
		header("Content-Disposition: attachment; filename=LaporanPegawai".date("dmY").".xls");
		header("Pragma: no-cache");
		header("Expires: 0");
	}
	else{
 ?>
</fieldset>
<?
	}
}
else{
	echo"<br><br><div align=center>DATA TIDAK DITEMUKAN ...</div>";
}
?>